package com.kh.pajx.sop.dao.school.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.kh.pajx.sop.base.BaseDaoImpl;
import com.kh.pajx.sop.dao.school.IHsSchoolInfoDao;
import com.kh.pajx.sop.domain.HsSchoolInfo;

/**
 * 学校信息持久化操作实现类
 * 
 * @author:	azzcsimp
 * @Createdate:	2014年7月24日 下午6:07:56
 */
@Repository
@SuppressWarnings("unchecked")
public class HsSchoolInfoDaoImpl extends BaseDaoImpl<HsSchoolInfo> implements IHsSchoolInfoDao {
	
	// ==>1.IHsSchoolInfoDao重载方法
	
	
	
	// ==>2.IBaseDao重载方法
	
	

	
	
	
	
	
	
	
	
	
	
	
	
	
	// =========================以下为需要整理或废弃方法===================================	
	/**
	 * 根据区域编码查找该地区最大编码的学校
	 * @see com.kh.pajx.sop.dao.school.IHsSchoolInfoDao#getMaxSchoolCodeByAreaCode(java.lang.String)
	 */
	@Override
	public String getMaxSchoolCodeByAreaCode(String areaCode) {
		Object obj = getSession()
				.createQuery(
						"SELECT MAX(scl.sclNumber) FROM HsSchoolInfo scl WHERE scl.areaCode = ?")
				.setParameter(0, areaCode).uniqueResult();
		if (obj != null) {
			return obj.toString();
		}
		return "";
	}

	/**
	 * 校验学校编码是否已存在
	 * @see com.kh.pajx.sop.dao.school.IHsSchoolInfoDao#checkSchoolNumber(java.lang.String)
	 */
	public long checkSchoolNumber(String schoolNumber) {
		return (Long) getSession()
				.createQuery(
						"SELECT COUNT(*) FROM HsSchoolInfo scl WHERE scl.sclNumber = ?")
				.setParameter(0, schoolNumber).uniqueResult();
	}

	public List getSchoolNameListBySpell(String spell,List sclNameList) {
		if(sclNameList!=null){
			return getSession().createQuery("SELECT s.sclId,s.sclName FROM HsSchoolInfo s " //
					+ "WHERE s.sclStatusFlag=1 and "//
					+ "(s.sclName like ? or s.sclSpell like ?) and "
					+" s.sclId in :sclNameList")
			.setParameter(0, "%" + spell + "%")
			.setParameter(1, "%" + spell.toUpperCase() + "%").setParameterList("sclNameList", sclNameList).list();
			
		}else{
			return getSession().createQuery("SELECT s.sclId,s.sclName FROM HsSchoolInfo s " //
					+ "WHERE s.sclStatusFlag=1 and "//
					+ "(s.sclName like ? or s.sclSpell like ?)")
					.setParameter(0, "%" + spell + "%")
					.setParameter(1, "%" + spell.toUpperCase() + "%").list();
		}
		
		
	}

	public String getMaxSchoolCodeByAreaCode2(String areaCode) {
		return getSession().createSQLQuery("select fun_getsclno(?) from dual")
				.setParameter(0, areaCode).uniqueResult().toString();
	}
	
	@Override
	public String getMaxSchoolNumber(){
		return getSession().createQuery("select max(scl.sclNumber) from HsSchoolInfo scl")
				.uniqueResult().toString();
	}

	public String getMaxSchoolCodeByAreaCode3(String areaCode) {
		String result = "-1";
		try {
			CallableStatement call = getSession().connection().prepareCall(
					"{call ?:=fun_getsclno(?)}");
			call.registerOutParameter(1, Types.VARCHAR);
			call.setString(2, areaCode);
			call.executeQuery();
			return call.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public String getMaxSchoolCodeByAreaCode4(final String areaCode) {
		final List<String> xxoo = new ArrayList<String>();
		getSession().doWork(new Work() {
			public void execute(Connection connection) {
				try {
					CallableStatement call = connection.prepareCall(
							"{call ?:=fun_getsclno(?)}");
					call.registerOutParameter(1, Types.VARCHAR);
					call.setString(2, areaCode);
					call.executeQuery();
					xxoo.add(call.getString(1));
				} catch (SQLException e) {
					e.printStackTrace();
					xxoo.add("-1");
				}
			}
		});
		return xxoo.get(0);
	}

	public List<String> getSclIdListBySale(String[] saleIds){
		/*return getSession().createQuery(//
				"SELECT scl.sclId FROM HsSchoolInfo scl,HsSchoolRelation rel WHERE scl.sclId=rel.sclId AND rel.relationStatusFlag=1 "  // 确定one-one
				+" and scl.sclStatusFlag=1 " // 主表状态正常
				+ " rel.saleUserId IN(:saleIds) order by scl.sclNumber")  // 查询条件
				.setParameterList("saleIds", saleIds)
				.list();*/
		
		return getSession().createQuery(//
				"select scl.sclId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.saleUserId IN(:saleIds) order by scl.sclNumber")
				.setParameterList("saleIds", saleIds)
				.list();
	}
	
	public List<String> getSclIdListBySale(List<String> saleIds){
		/*return getSession().createQuery(//
				"SELECT scl.sclId FROM HsSchoolInfo scl,HsSchoolRelation rel WHERE scl.sclId=rel.sclId AND rel.relationStatusFlag=1 "  // 确定one-one
				+" and scl.sclStatusFlag=1 " // 主表状态正常
				+ " and rel.saleUserId IN(:saleIds) order by scl.sclNumber")  // 查询条件
				.setParameterList("saleIds", saleIds)
				.list();*/
		return getSession().createQuery(//
				"select scl.sclId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.saleUserId IN(:saleIds) order by scl.sclNumber")
				.setParameterList("saleIds", saleIds)
				.list();
	}
	
	public List<String> getSclIdListBySale(String saleId){
		/*return getSession().createQuery(//
				"SELECT scl.sclId FROM HsSchoolInfo scl,HsSchoolRelation rel WHERE scl.sclId=rel.sclId AND rel.relationStatusFlag=1 "  // 确定one-one
				+" and scl.sclStatusFlag=1 " // 主表状态正常
				+ " and rel.saleUserId =? order by scl.sclNumber")  // 查询条件
				.setParameter("0", saleId)
				.list();*/
		return getSession().createQuery(//
				"select scl.sclId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.saleUserId=? order by scl.sclNumber")
				.setParameter(0, saleId)
				.list();
	}

	@Override
	public List<String> getEcIdListByBind() {
		/*StringBuilder sb=new StringBuilder("");
		sb.append("select sr.ecId from HsSchoolInfo s,HsSchoolRelation sr where s.sclId=sr.sclId and s.sclStatusFlag=1 and sr.relationStatusFlag=1")
		  .append(" and sr.ecId is not null");
		return getSession().createQuery(sb.toString()).list();*/
		return getSession().createQuery("select scl.ecId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.ecId is not null").list();
	}

	
	@Override
	public List<String> getAllSclId() {
		return getSession().createQuery(//
				"SELECT scl.sclId FROM HsSchoolInfo scl WHERE scl.sclStatusFlag=1 order by scl.sclNumber").list();
	}
	
	@Override
	public List<String> getSclIdListByAreaCode(List<String> areaCodes) {
		return getSession().createQuery(//
				"select scl.sclId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.areaCode IN(:areaCodes) order by scl.sclNumber")
				.setParameterList("areaCodes", areaCodes)
				.list();
	}
	
	
	public List<String> getSclIdListByKeep(String keepId){
		/*return getSession().createQuery(//
				"SELECT scl.sclId FROM HsSchoolInfo scl,HsSchoolRelation rel WHERE scl.sclId=rel.sclId AND rel.relationStatusFlag=1 "  // 确定one-one
				+" and scl.sclStatusFlag=1 " // 主表状态正常
				+ " and rel.saleUserId =? order by scl.sclNumber")  // 查询条件
				.setParameter("0", saleId)
				.list();*/
		return getSession().createQuery(//
				"select scl.sclId from HsSchoolInfo scl where scl.sclStatusFlag='1' and scl.saleKeepId=? order by scl.sclNumber")
				.setParameter(0, keepId)
				.list();
	}
	
	
	@Override
	public int countSclBySpell(String spell) throws Exception{
		Long count = (Long)getSession().createQuery(//
				"SELECT count(*) FROM HsSchoolInfo scl where scl.sclSpell=?") //
				.setParameter(0, spell)
				.uniqueResult();
		Integer sum = new Integer(String.valueOf(count));  
		return sum;
	}

}
